package com.studorm.xml.action;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;

import org.apache.struts2.ServletActionContext;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.Node;

import com.opensymphony.xwork2.ActionSupport;
import com.studorm.Connection_mysql;
import com.studorm.XMLOption;
import com.studorm.vo.Utilities;

/* －－－－－－－－－－－－－－－－－
 * 		水电费操作类
 * －－－－－－－－－－－－－－－－－
 * 对水电费的操作均写在此
 * 前台请求后，带有get函数的变量
 * 均被转成JSON格式传到前台
 * 					by Huang
 */
public class UtilitiesAction extends ActionSupport {

	/**
	 * 
	 */
	private static final long serialVersionUID = -3697921271744988026L;

	// 此变量为存放你要读取的表格数据，前台请求后会自动生成JSON格式发送到客户端
	private List<Utilities> tableRow = new ArrayList<Utilities>();
	private int amountOfRow = 0;
	public List<Utilities> getTableRow() {
		return tableRow;
	}
	public void setTableRow(List<Utilities> tableRow) {
		this.tableRow = tableRow;
	}
	public String loadUtilities() throws Exception{
		//读取请求的参数 
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String dormBdName = request.getParameter("dormBdName"); 
		String dormNu = request.getParameter("dormNu");
		String year = request.getParameter("year");
		String rowEachP_t = request.getParameter("rowEachP");//每页行数
		String nuOfP_t = request.getParameter("nuOfP");//要获取第几页的数据
		if(dormBdName == null || dormNu == null || year == null){
			return ERROR;
		}

		int rowEachP = 10; //每页行数
		int nuOfP = 1;//当前是第几页
		if(rowEachP_t != null){
			rowEachP = Integer.parseInt(rowEachP_t);
		}
		if(nuOfP_t != null){
			nuOfP = Integer.parseInt(nuOfP_t);
		}

		/* ---------------------------------------
		 * 		从dorminfs.xml 获取宿舍ID ver.12.18
		 * ---------------------------------------
		 * 
		 */
		String dorminfsPath=request.getSession().getServletContext().getRealPath("\\db\\xml\\dorminfs.xml");
		Document dorminfsDoc = XMLOption.loadXML(dorminfsPath);
		
		/*
		 * 读取选择的宿舍楼的所有宿舍id号
		 * 并合并成条件，用于读取整楼宿舍的
		 * 水电费的条件。如：
		 *  dorm=1 or dorm=3...
		 */
		StringBuilder dormidCondition = null;
		if(dormNu.equals("全部")){
			List<? extends Node> list = dorminfsDoc.selectNodes("/dorminfs/dorminf[dormBdName='"+dormBdName+"']");
			Iterator<? extends Node> it = list.iterator();
			dormidCondition = new StringBuilder();
			while(it.hasNext()){
				dormidCondition.append("dorm="+it.next().valueOf("@id"));
				if(it.hasNext()){
					dormidCondition.append(" or ");
				}
			}
		}else{
			List<? extends Node> list = dorminfsDoc.selectNodes("/dorminfs/dorminf[dormBdName='"+dormBdName+"' and dormNu='"+dormNu+"']");
			Iterator<? extends Node> it = list.iterator();
			dormidCondition = new StringBuilder();
			while(it.hasNext()){
				dormidCondition.append("dorm="+it.next().valueOf("@id"));
				if(it.hasNext()){
					dormidCondition.append(" or ");
				}
			}
		}
		
		String utilitiesPath=request.getSession().getServletContext().getRealPath("\\db\\xml\\utilities.xml");
		Document utilitiesDoc = XMLOption.loadXML(utilitiesPath);
		List<? extends Node> list = utilitiesDoc.selectNodes("/utilities/utility[("+dormidCondition.toString()+") and starts-with(date,"+year+")]");
		amountOfRow = list.size();
		
		for(int count = (nuOfP-1) * rowEachP; count < (nuOfP-1) * rowEachP+rowEachP && count < list.size(); ++count){
			Node n = list.get(count);
			Utilities u = new Utilities();
			u.setDormBdName(dormBdName);
			
			// 在宿舍表里通过宿舍的id查询宿舍号
			Node dornNuNode = dorminfsDoc.selectSingleNode("dorminfs/dorminf[@id="+n.valueOf("dorm")+"]/dormNu");
			u.setDormNu(dornNuNode.getText());
			
			u.setElectricity(Double.parseDouble(n.valueOf("electricity")));
			u.setId(Integer.parseInt(n.valueOf("@id")));
			u.setIspayE(Integer.parseInt(n.valueOf("ispayE")));
			u.setIspayW(Integer.parseInt(n.valueOf("ispayW")));
			u.setMonth(n.valueOf("date").substring(5, 7));
			u.setWater(Double.parseDouble(n.valueOf("water")));
			u.setYear(n.valueOf("date").substring(0, 4));
			u.setElectricityConsumption(Double.parseDouble(n.valueOf("electricityConsumption")));
			u.setWaterConsumption(Double.parseDouble(n.valueOf("waterConsumption")));
			
			tableRow.add(u);
		}

		return SUCCESS;
	}
	public int getAmountOfRow() {
		return amountOfRow;
	}
	public void setAmountOfRow(int amountOfRow) {
		this.amountOfRow = amountOfRow;
	}

	
	/* ---------------------------
	 * 		删除水电费项 ver.11.23
	 * ---------------------------
	 * 
	 * 从前台ajax请求获得delId参数
	 * 此参数为水电费记录的ID，通过此ID
	 * 用SQL语句删除数据库中的记录
	 * 					by Huang
	 */
	public String delUtilities() throws Exception{
		//读取请求的参数 
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String uId = request.getParameter("delId");

		if(uId == null){
			return NONE;
		}
		
		String utiliesPath=request.getSession().getServletContext().getRealPath("\\db\\xml\\utilities.xml");
		Document utiliesDoc = XMLOption.loadXML(utiliesPath);
		Node delNode = utiliesDoc.selectSingleNode("/utilities/utility[@id='"+uId+"']");
		if(delNode == null){
			return NONE;
		}
		Boolean succ = delNode.getParent().remove(delNode);
		
		if(succ){
			XMLOption.writeXml(utiliesDoc, utiliesPath);
			return SUCCESS;
		}else{
			return NONE;
		}
	}
	
	/* --------------------------------
	 * 		添加水电费信息 ver.11.23
	 * --------------------------------
	 * 函数中的Pattern用来通过正则表达式验证输入
	 * 的字符串是否符合输入格式
	 * 
	 */
	public String addUtilities() throws Exception{
		
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String dormBdName = request.getParameter("dormBdName");
		String dormNu = request.getParameter("dormNu");
		String yearMonth = request.getParameter("yearMonth");
		String water = request.getParameter("water");
		String electricity = request.getParameter("electricity");
		String waterC = request.getParameter("waterC");
		String electricityC = request.getParameter("electricityC");

		Pattern patternNu = Pattern.compile("\\d{3}");// 匹配3位整数
		Pattern patternDeci = Pattern.compile("\\d*\\.{0,1}\\d*"); // 匹配小数或整数
		Pattern patternYM = Pattern.compile("[1-2]\\d*-[0-1]{0,1}[0-9]");// 匹配 ????-??的年月格式
		
		if(dormBdName.isEmpty() || dormBdName.equals("请选择")){
			return NONE;
		}
		if(dormNu == null || dormNu.isEmpty() || !patternNu.matcher(dormNu).matches()){
			return NONE;
		}
		if(yearMonth == null || yearMonth.isEmpty() || !patternYM.matcher(yearMonth).matches()){
			return NONE;
		}
		if(water == null || water.isEmpty() || !patternDeci.matcher(water).matches()){
			return NONE;
		}
		if(electricity == null || electricity.isEmpty() || !patternDeci.matcher(electricity).matches()){
			return NONE;
		}
		if(waterC == null || waterC.isEmpty() || !patternDeci.matcher(waterC).matches()){
			return NONE;
		}
		if(electricityC == null || electricityC.isEmpty() || !patternDeci.matcher(electricityC).matches()){
			return NONE;
		}

		// 获取宿舍ID
		String dorminfsPath=request.getSession().getServletContext().getRealPath("\\db\\xml\\dorminfs.xml");
		Document dorminfsDoc = XMLOption.loadXML(dorminfsPath);
		Node dormId = dorminfsDoc.selectSingleNode("/dorminfs/dorminf[dormBdName='"+dormBdName+"' and dormNu='"+dormNu+"']/@id");
		
		// 获取当前水电费ID递增数作为新水电费记录的ID，加1后保存以下次新增时使用
		String utiliesPath=request.getSession().getServletContext().getRealPath("\\db\\xml\\utilities.xml");
		Document utiliesDoc = XMLOption.loadXML(utiliesPath);
		Node countNode = utiliesDoc.selectSingleNode("/utilities/count");
		String count = countNode.getText();
		countNode.setText(String.valueOf(Integer.parseInt(count)+1));
		
		// 新增utility节点，设置属性id
		Element utilities = utiliesDoc.getRootElement();
		Element utility = utilities.addElement("utility");
		utility.addAttribute("id", count);
		
		// 新增dorm节点
		Element dorm = utility.addElement("dorm");
		dorm.setText(dormId.getText());
		
		Element date = utility.addElement("date");
		date.setText(yearMonth);
		
		Element electricityConsumption = utility.addElement("electricityConsumption");
		electricityConsumption.setText(electricityC);
		
		Element electricityE = utility.addElement("electricity");
		electricityE.setText(electricity);
		
		Element waterConsumption = utility.addElement("waterConsumption");
		waterConsumption.setText(waterC);
		
		Element waterE = utility.addElement("water");
		waterE.setText(water);
		
		Element ispayW = utility.addElement("ispayW");
		ispayW.setText("0");
		
		Element ispayE = utility.addElement("ispayE");
		ispayE.setText("0");
		
		// 保存
		XMLOption.writeXml(utiliesDoc, utiliesPath);
		

		return SUCCESS;

	}
	
	/* --------------------------------
	 * 		编辑水电费信息 ver.11.24
	 * --------------------------------
	 * 函数中的Pattern用来通过正则表达式验证输入
	 * 的字符串是否符合输入格式
	 * 
	 */
	public String editUtilities() throws Exception{
		
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String yearMonth = request.getParameter("yearMonth");
		String water = request.getParameter("water");
		String electricity = request.getParameter("electricity");
		String waterC = request.getParameter("waterC");
		String electricityC = request.getParameter("electricityC");
		String id = request.getParameter("uid");

		Pattern patternNu = Pattern.compile("\\d*");
		Pattern patternDeci = Pattern.compile("\\d*\\.{0,1}\\d*"); // 匹配小数或整数
		Pattern patternYM = Pattern.compile("[1-2]\\d*-[0-1]{0,1}[0-9]");// 匹配 ????-??的年月格式
		
		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
			return NONE;
		}

		if(yearMonth == null || yearMonth.isEmpty() || !patternYM.matcher(yearMonth).matches()){
			return NONE;
		}
		if(water == null || water.isEmpty() || !patternDeci.matcher(water).matches()){
			return NONE;
		}
		if(electricity == null || electricity.isEmpty() || !patternDeci.matcher(electricity).matches()){
			return NONE;
		}
		if(waterC == null || waterC.isEmpty() || !patternDeci.matcher(waterC).matches()){
			return NONE;
		}
		if(electricityC == null || electricityC.isEmpty() || !patternDeci.matcher(electricityC).matches()){
			return NONE;
		}

		String utiliesPath=request.getSession().getServletContext().getRealPath("\\db\\xml\\utilities.xml");
		Document utiliesDoc = XMLOption.loadXML(utiliesPath);
		Node ispayWNode = utiliesDoc.selectSingleNode("/utilities/utility[@id='"+id+"']");
		
		(ispayWNode.selectSingleNode("date")).setText(yearMonth);
		(ispayWNode.selectSingleNode("electricity")).setText(electricity);
		(ispayWNode.selectSingleNode("electricityConsumption")).setText(electricityC);
		(ispayWNode.selectSingleNode("waterConsumption")).setText(waterC);
		(ispayWNode.selectSingleNode("water")).setText(water);

		XMLOption.writeXml(utiliesDoc, utiliesPath);
		
		return SUCCESS;
	}
	
	/* -----------------------------
	 * 	只读取一行指定水电费记录 ver.11.24
	 * -----------------------------
	 * 
	 */
	public String loadAUtilities() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String utilId = request.getParameter("editId");
		Pattern patternNu = Pattern.compile("\\d*");
		if(utilId == null || utilId.isEmpty() || !patternNu.matcher(utilId).matches()){
			return NONE;
		}
		
		String utiliesPath=request.getSession().getServletContext().getRealPath("\\db\\xml\\utilities.xml");
		Document utiliesDoc = XMLOption.loadXML(utiliesPath);
		Node utiliNode = utiliesDoc.selectSingleNode("/utilities/utility[@id='"+utilId+"']");

		String dorminfsPath=request.getSession().getServletContext().getRealPath("\\db\\xml\\dorminfs.xml");
		Document dorminfsDoc = XMLOption.loadXML(dorminfsPath);
		Node dormNode = dorminfsDoc.selectSingleNode("/dorminfs/dorminf[@id='"+utiliNode.valueOf("dorm")+"']");
		
		Utilities u = new Utilities();
		u.setDormBdName(dormNode.valueOf("dormBdName"));
		u.setDormNu(dormNode.valueOf("dormNu"));
		u.setElectricity(Double.parseDouble(utiliNode.valueOf("electricity")));
		u.setId(Integer.parseInt(utiliNode.valueOf("@id")));
		u.setMonth(String.format("%02d", Integer.parseInt(utiliNode.valueOf("date").substring(5, 7))));//格式化月份为两位数，不足前面补0
		u.setWater(Double.parseDouble(utiliNode.valueOf("water")));
		u.setYear(String.format("%02d", Integer.parseInt(utiliNode.valueOf("date").substring(0, 4))));
		u.setElectricityConsumption(Double.parseDouble(utiliNode.valueOf("electricityConsumption")));
		u.setWaterConsumption(Double.parseDouble(utiliNode.valueOf("waterConsumption")));
		
		tableRow.add(u);
		
		return SUCCESS;
	}
	
	/* -----------------------------
	 * 		修改水费是否缴纳 ver.11.26
	 * -----------------------------
	 * 
	 */
	public String alterPayW() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String id = request.getParameter("alterId");
		String status = request.getParameter("status");
		Pattern patternNu = Pattern.compile("\\d*");
		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
			return NONE;
		}
		
		String utiliesPath=request.getSession().getServletContext().getRealPath("\\db\\xml\\utilities.xml");
		Document utiliesDoc = XMLOption.loadXML(utiliesPath);
		Node ispayWNode = utiliesDoc.selectSingleNode("/utilities/utility[@id='"+id+"']/ispayW");
		
		if(status.equals("未交")){
			ispayWNode.setText("1");
		}else if(status.equals("已交")){
			ispayWNode.setText("0");
		}else{
			return NONE;
		}
		XMLOption.writeXml(utiliesDoc, utiliesPath);

		return SUCCESS;

	}
	
	
	/* -----------------------------
	 * 		修改电费是否缴纳 ver.11.26
	 * -----------------------------
	 * 
	 */
	public String alterPayE() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String id = request.getParameter("alterId");
		String status = request.getParameter("status");
		Pattern patternNu = Pattern.compile("\\d*");
		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
			return NONE;
		}
		
		String utiliesPath=request.getSession().getServletContext().getRealPath("\\db\\xml\\utilities.xml");
		Document utiliesDoc = XMLOption.loadXML(utiliesPath);
		Node ispayWNode = utiliesDoc.selectSingleNode("/utilities/utility[@id='"+id+"']/ispayE");
		
		if(status.equals("未交")){
			ispayWNode.setText("1");
		}else if(status.equals("已交")){
			ispayWNode.setText("0");
		}else{
			return NONE;
		}
		XMLOption.writeXml(utiliesDoc, utiliesPath);

		return SUCCESS;
	}
}
